package com.lagou.dao.impl;

import com.lagou.dao.CourseContentDao;
import com.lagou.pojo.Course;
import com.lagou.pojo.Course_Lesson;
import com.lagou.pojo.Course_Section;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.management.Query;
import java.sql.SQLException;
import java.util.List;

public class CourseContentDaoImpl implements CourseContentDao {

    /**
     * 根据课程ID查询课程相关信息
     * @param courseId
     * @return
     */
    @Override
    public List<Course_Section> findSectionAndLessonByCourseId(int courseId) {

        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "SELECT\n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_name,\n" +
                    "description,\n" +
                    "order_num\n" +
                    "FROM course_section cs WHERE course_id = ? ;";

            List<Course_Section> sectionList = queryRunner.query(sql, new BeanListHandler<Course_Section>(Course_Section.class), courseId);

            for (Course_Section item : sectionList) {
                int sectionId = item.getId();

                List<Course_Lesson> lessonList = findLessonBySectionId(sectionId);

                item.setLessonList(lessonList);
            }

            return sectionList;

        } catch (SQLException e) {

            e.printStackTrace();

            return null;
        }
    }

    /**
     * 根据章节ID 查询章节相关的课时信息
     * @param sectionId
     * @return
     */
    @Override
    public List<Course_Lesson> findLessonBySectionId(int sectionId) {
        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "SELECT\n" +
                    "id,\n" +
                    "course_id,\n" +
                    "section_id,\n" +
                    "theme,\n" +
                    "duration,\n" +
                    "is_free,\n" +
                    "order_num\n" +
                    "FROM course_lesson WHERE section_id = ?;";

            List<Course_Lesson> lessonList = queryRunner.query(sql, new BeanListHandler<Course_Lesson>(Course_Lesson.class), sectionId);

            return lessonList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }

    /**
     * 添加章节时进行数据回显
     * @param courseId
     * @return
     */
    @Override
    public Course findCourseByCourseId(int courseId) {
        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "SELECT id,course_name FROM course WHERE id = ?";

            Course course = queryRunner.query(sql, new BeanHandler<Course>(Course.class), courseId);

            return course;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 保存章节信息
     * @param section
     * @return
     */
    @Override
    public int saveSection(Course_Section section) {
        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "INSERT INTO\n" +
                    "course_section(course_id,section_name,description,order_num,STATUS,create_time,update_time)\n" +
                    "VALUES(?,?,?,?,?,?,?);";

            Object[] param = {
                    section.getCourse_id(), section.getSection_name(), section.getDescription(), section.getOrder_num(),
                    section.getStatus(), section.getCreate_time(), section.getUpdate_time()
            };
            int row = queryRunner.update(sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 修改章节信息
     * @param section
     * @return
     */
    @Override
    public int updateSection(Course_Section section) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL
            String sql = "UPDATE course_section SET\n" +
                    "section_name = ?,\n" +
                    "description = ?,\n" +
                    "order_num = ?,\n" +
                    "update_time = ?  WHERE id = ?;";

            //3.准备参数
            Object[] param =
                    {section.getSection_name(),section.getDescription(),section.getOrder_num(),
                            section.getUpdate_time(),section.getId()};

            //4.执行修改操作
            int row = qr.update(sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 修改章节的状态
     * @param id
     * @param status
     * @return
     */
    @Override
    public int updateSectionStatus(int id, int status) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL
            String sql = "UPDATE course_section SET STATUS = ?,update_time = ? WHERE id = ?;";

            //3.准备参数
            Object[] param = {status , DateUtils.getDateFormart(),id};

            //4.执行修改操作
            int row = qr.update(sql, param);

            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 保存课时信息
     * @param lesson
     * @return
     */
    @Override
    public int saveLesson(Course_Lesson lesson) {
        try {
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

            String sql = "INSERT INTO\n" +
                    "course_lesson(course_id,section_id,theme,duration,is_free,order_num,create_time,update_time)\n" +
                    "VALUES(?,?,?,?,?,?,?,?);";

            Object[] param = {
                    lesson.getCourse_id(), lesson.getSection_id(), lesson.getTheme(), lesson.getDuration(),
                    lesson.getIs_free(), lesson.getOrderNum(), lesson.getCreate_time(), lesson.getUpdate_time()
            };
            int row = queryRunner.update(sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 修改课时信息
     * @param lesson
     * @return
     */
    @Override
    public int updateLesson(Course_Lesson lesson) {
        try {
            //1.创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2.编写SQL
            String sql = "UPDATE course_lesson SET\n" +
                    "theme = ?,\n" +
                    "is_free = ?,\n" +
                    "order_num = ?,\n" +
                    "update_time = ?  WHERE id = ?;";

            //3.准备参数
            Object[] param =
                    {lesson.getTheme(),lesson.getIs_free(),lesson.getOrderNum(),
                            lesson.getUpdate_time(),lesson.getId()};

            //4.执行修改操作
            int row = qr.update(sql, param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}
